﻿

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Collections;
    using System.Collections.Generic;

namespace FShop.DAL{

    ///<summary>
    ///The Class Provides methods for common database table operations. 
    ///</summary>
    ///<remarks>
    ///Changes to this file may cause incorrect behavior and will be lost if  the code is regenerated.
    ///Update the <see cref="Invetory_DAL"/> class if you need to add or change some functionality
    ///</remarks>
    public class Invetory_DAL:BaseObject  {

        private const string SP_SELECT_BY_PRIMARY_KEY="usp_Invetory_SelectByPrimaryKey";
        private const string SP_SELECT_ROWS_BY_WHERE="usp_Invetory_SelectRowsByWhere";
        private const string SP_INSERT="usp_Invetory_Insert";
        private const string SP_UPDATE="usp_Invetory_Update";
        private const string SP_DELETE="usp_Invetory_DeleteByPrimaryKey";
        private const string SP_DELETE_WHERE="usp_Invetory_DeleteWhere";
        private const string SP_SELECT_GENERAL="usp_Invetory_SelectGeneral";
        private const string SP_SELECT_PAGING="usp_Invetory_SelectPaging";

        #region Constructor

        ///<summary>
        ///Initializes a new instance of the <see cref="InvetoryDal"/> 
        ///class with the specified <see cref="BaseDAL"/>.
        ///</summary>
        public Invetory_DAL(): base()
        {

        }

        #endregion

        #region Public Methods
        ///<summary>
        ///Create Obj from ireader.
        ///</summary>

        private Invetory GetObjectFromReader(IDataReader reader) 
         {
             Invetory temp= new Invetory();
             temp.Inv_ID = (reader["Inv_ID"] is DBNull) ? string.Empty :(System.String)reader["Inv_ID"] ;
             temp.Inv_CreateDate = (reader["Inv_CreateDate"] is DBNull) ? DateTime.MinValue :(System.DateTime)reader["Inv_CreateDate"] ;
             temp.Inv_TotalPrice = (reader["Inv_TotalPrice"] is DBNull) ? int.MinValue :(System.Double)reader["Inv_TotalPrice"] ;
             temp.Inv_TotalQuantity = (reader["Inv_TotalQuantity"] is DBNull) ? int.MinValue :(System.Int32)reader["Inv_TotalQuantity"] ;
             temp.Int_Status = (reader["Int_Status"] is DBNull) ? int.MinValue :(System.Int32)reader["Int_Status"] ;
             temp.User_ID = (reader["User_ID"] is DBNull) ? int.MinValue :(System.Int32)reader["User_ID"] ;
             temp.Inv_UserRevice = (reader["Inv_UserRevice"] is DBNull) ? string.Empty :(System.String)reader["Inv_UserRevice"] ;
             temp.Inv_UserRevicePhone = (reader["Inv_UserRevicePhone"] is DBNull) ? string.Empty :(System.String)reader["Inv_UserRevicePhone"] ;
             temp.Inv_UserReviceAddress = (reader["Inv_UserReviceAddress"] is DBNull) ? string.Empty :(System.String)reader["Inv_UserReviceAddress"] ;
             temp.Inv_PayType = (reader["Inv_PayType"] is DBNull) ? int.MinValue :(System.Int32)reader["Inv_PayType"] ;
             temp.Inv_DateRevice = (reader["Inv_DateRevice"] is DBNull) ? DateTime.MinValue :(System.DateTime)reader["Inv_DateRevice"] ;
             temp.Local_ID = (reader["Local_ID"] is DBNull) ? int.MinValue :(System.Int32)reader["Local_ID"] ;
             temp.Inv_Note = (reader["Inv_Note"] is DBNull) ? string.Empty :(System.String)reader["Inv_Note"] ;
             temp.Inv_UserReviceEmail = (reader["Inv_UserReviceEmail"] is DBNull) ? string.Empty :(System.String)reader["Inv_UserReviceEmail"] ;
              return temp;
         }
        ///<summary>
        ///Get List Invetory by a primary key in object 
        ///</summary>
		public Invetory Get_Invetory_ByID(System.String Inv_ID)
		{
		    SqlConnection conn = null;
		    try
		        {
		            using( conn = CreateConnection() )
		            {
                         SqlCommand cmd = new SqlCommand(SP_SELECT_BY_PRIMARY_KEY, conn);
                         cmd.CommandType = CommandType.StoredProcedure;
				        cmd.Parameters.Add(new SqlParameter("@Inv_ID",Inv_ID));
                         conn.Open();
                         Invetory temp = new Invetory();
                         using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection) )
                         {
                             while (reader.Read())
                                 {
                                     temp=( GetObjectFromReader(reader) );
                                 }
                         }
                     conn.Close();
                     return temp;
		            }
		        }
                catch
		        {
                     return null;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		 }

        ///<summary>
        ///Get List Invetory and store in a list 
        ///</summary>
		public List<Invetory> GetList_Invetory()
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(SP_SELECT_ROWS_BY_WHERE, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@SqlWhere",""));
                conn.Open();
                List<Invetory> lst = new List<Invetory>();
                using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection) )
                {
                    while (reader.Read())
                     {
                         lst.Add( GetObjectFromReader(reader) );
                     }
                }
                conn.Close();
                return lst;
		    }
		 }

		public void Delete_Invetory(Invetory temp)
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(SP_DELETE, conn);
                cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.Add(new SqlParameter("@Inv_ID",temp.Inv_ID));
                try
		        {
                 conn.Open();
                 cmd.ExecuteNonQuery();
                 conn.Close();
		        }
                catch(SqlException ex)
		        {
		            throw ex;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		    }
		 }

		public void Delete_Invetory_Where(string whereSql)
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(SP_DELETE_WHERE, conn);
                cmd.CommandType = CommandType.StoredProcedure;
			    cmd.Parameters.Add(new SqlParameter("@Where",whereSql));
                try
		        {
                 conn.Open();
                 cmd.ExecuteNonQuery();
                 conn.Close();
		        }
                catch(SqlException ex)
		        {
		            throw ex;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		    }
		 }

        ///<summary>
        ///Get List Invetory as DataTable 
        ///</summary>
        /// <param name="whereSql">Where fields. example : 1=1 </param>
        /// <param name="orderBy"Order fields></param>
		public DataTable Get_InvetoryAsTable(string whereSql,string orderBy)
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(SP_SELECT_GENERAL, conn);
                cmd.CommandType = CommandType.StoredProcedure;
			    cmd.Parameters.Add(new SqlParameter("@SqlWhere",whereSql));
			    cmd.Parameters.Add(new SqlParameter("@SqlOrderBy",orderBy));
                SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                try
		       {
                 conn.Open();
                 DataTable tbl = new DataTable();
                 dataAdapter.Fill(tbl);
                 conn.Close();
                 return tbl;
		       }
                catch
		        {
                     return null;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		    }
		 }

        ///<summary>
        ///Using for paging data 
        ///</summary>
        /// <param name="pageIndex">Number of pages</param>
        /// <param name="PageSize">Number of a rows per page</param>
        /// <param name="conditions">Where  fields</param>
        /// <param name="groupBy">Group by fields</param>
		public DataTable Get_InvetoryAsPaging(int pageIndex,int pageSize,string sSortColumn,string sFields,string sWhere,string sGroupBy)
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(SP_SELECT_PAGING, conn);
                cmd.CommandType = CommandType.StoredProcedure;
			    cmd.Parameters.Add(new SqlParameter("@PageIndex",pageIndex));
			    cmd.Parameters.Add(new SqlParameter("@PageSize",pageSize));
			    cmd.Parameters.Add(new SqlParameter("@SortColumn",sSortColumn));
			    cmd.Parameters.Add(new SqlParameter("@Fields",sFields));
			    cmd.Parameters.Add(new SqlParameter("@Where",sWhere));
			    cmd.Parameters.Add(new SqlParameter("@Group",sGroupBy));
                SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                try
		       {
                 conn.Open();
                 DataTable tbl = new DataTable();
                 dataAdapter.Fill(tbl);
                 conn.Close();
                 return tbl;
		       }
                catch
		        {
                     return null;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		    }
		 }

        ///<summary>
        ///Insert new Invetory
        ///</summary>
		public void Insert_Invetory(Invetory temp)
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(SP_INSERT, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Inv_ID", temp.Inv_ID));
				cmd.Parameters.Add(new SqlParameter("@Inv_CreateDate",temp.Inv_CreateDate));
				cmd.Parameters.Add(new SqlParameter("@Inv_TotalPrice",temp.Inv_TotalPrice));
				cmd.Parameters.Add(new SqlParameter("@Inv_TotalQuantity",temp.Inv_TotalQuantity));
				cmd.Parameters.Add(new SqlParameter("@Int_Status",temp.Int_Status));
				cmd.Parameters.Add(new SqlParameter("@User_ID",temp.User_ID));
				cmd.Parameters.Add(new SqlParameter("@Inv_UserRevice",temp.Inv_UserRevice));
				cmd.Parameters.Add(new SqlParameter("@Inv_UserRevicePhone",temp.Inv_UserRevicePhone));
				cmd.Parameters.Add(new SqlParameter("@Inv_UserReviceAddress",temp.Inv_UserReviceAddress));
				cmd.Parameters.Add(new SqlParameter("@Inv_PayType",temp.Inv_PayType));
				cmd.Parameters.Add(new SqlParameter("@Inv_DateRevice",temp.Inv_DateRevice));
				cmd.Parameters.Add(new SqlParameter("@Local_ID",temp.Local_ID));
				cmd.Parameters.Add(new SqlParameter("@Inv_Note",temp.Inv_Note));
				cmd.Parameters.Add(new SqlParameter("@Inv_UserReviceEmail",temp.Inv_UserReviceEmail));
                try
		        {
                 conn.Open();
                 cmd.ExecuteNonQuery();
                 conn.Close();
		        }
                catch(SqlException ex)
		        {
		            throw ex;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		    }
		 }

        ///<summary>
        ///Update a Invetory
        ///</summary>
		public void Update_Invetory(Invetory temp)
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(SP_UPDATE, conn);
                cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.Add(new SqlParameter("@Inv_ID",temp.Inv_ID));
				cmd.Parameters.Add(new SqlParameter("@Inv_CreateDate",temp.Inv_CreateDate));
				cmd.Parameters.Add(new SqlParameter("@Inv_TotalPrice",temp.Inv_TotalPrice));
				cmd.Parameters.Add(new SqlParameter("@Inv_TotalQuantity",temp.Inv_TotalQuantity));
				cmd.Parameters.Add(new SqlParameter("@Int_Status",temp.Int_Status));
				cmd.Parameters.Add(new SqlParameter("@User_ID",temp.User_ID));
				cmd.Parameters.Add(new SqlParameter("@Inv_UserRevice",temp.Inv_UserRevice));
				cmd.Parameters.Add(new SqlParameter("@Inv_UserRevicePhone",temp.Inv_UserRevicePhone));
				cmd.Parameters.Add(new SqlParameter("@Inv_UserReviceAddress",temp.Inv_UserReviceAddress));
				cmd.Parameters.Add(new SqlParameter("@Inv_PayType",temp.Inv_PayType));
				cmd.Parameters.Add(new SqlParameter("@Inv_DateRevice",temp.Inv_DateRevice));
				cmd.Parameters.Add(new SqlParameter("@Local_ID",temp.Local_ID));
				cmd.Parameters.Add(new SqlParameter("@Inv_Note",temp.Inv_Note));
				cmd.Parameters.Add(new SqlParameter("@Inv_UserReviceEmail",temp.Inv_UserReviceEmail));
                try
		        {
                 conn.Open();
                 cmd.ExecuteNonQuery();
                 conn.Close();
		        }
                catch(SqlException ex)
		        {
		            throw ex;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		    }
		 }

#endregion
 }
}
